package com.yq.dao;

import com.yq.bean.ClockWork;
import com.yq.bean.Clock_info;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class ClockDao {
    //上班打卡
    public boolean in_clock(Connection con, Clock_info ci) throws SQLException {
        String sql = "insert into t_clock_info(employee_no,clock_in_time,clock_date) values (?,?,?) ";
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setString(1,ci.getEmploy_no());
        ps.setString(2,ci.getClock_in_time());
        ps.setString(3,ci.getClock_date());
        int i = ps.executeUpdate();
        return i > 0;
    }

    //下班打卡
    public boolean off_clock(Connection con, Clock_info ci) throws SQLException {
        String sql = "update t_clock_info set clock_off_time=? where employee_no=? and clock_date=?";
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setString(1,ci.getClock_off_time());
        ps.setString(2,ci.getEmploy_no());
        ps.setString(3,ci.getClock_date());
        int i = ps.executeUpdate();
        return i > 0;
    }

    //下班打卡（上班没打卡）
    public boolean off_clock2(Connection con, Clock_info ci) throws SQLException {
        String sql = "insert into t_clock_info(employee_no,clock_off_time,clock_date) values (?,?,?) ";
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setString(1,ci.getEmploy_no());
        ps.setString(2,ci.getClock_off_time());
        ps.setString(3,ci.getClock_date());
        int i = ps.executeUpdate();
        return i > 0;
    }

    //看现在时间
    public Integer time(Connection con) throws SQLException {
        String sql = "SELECT NOW()<(\n" +
                "SELECT DATE_FORMAT(NOW(),'%Y-%m-%d 12:00:00'))";
        PreparedStatement ps = con.prepareStatement(sql);
        ResultSet resultSet = ps.executeQuery();
        if (resultSet.next()){
            return resultSet.getInt(1);
        }
        return null;
    }

    //检查是不是上班日
    public boolean check_clock_date(Connection con) throws SQLException {
        String sql = "SELECT * FROM t_work_date WHERE DATEDIFF(work_date,NOW())=0";
        PreparedStatement ps = con.prepareStatement(sql);
        ResultSet resultSet = ps.executeQuery();
        return resultSet.next();
    }

    //检查有没有打卡上班
    public boolean check_clock(Connection con, Clock_info ci) throws SQLException {
        String sql = "select * from t_clock_info where employee_no=? and clock_date=? and clock_in_time is not null";
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setString(1,ci.getEmploy_no());
        ps.setString(2,ci.getClock_date());
        ResultSet resultSet = ps.executeQuery();
        return resultSet.next();
    }

    //检查有没有打卡下班
    public boolean check_clock2(Connection con, Clock_info ci) throws SQLException {
        String sql = "select * from t_clock_info where employee_no=? and clock_date=? and clock_off_time is not null";
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setString(1,ci.getEmploy_no());
        ps.setString(2,ci.getClock_date());
        ResultSet resultSet = ps.executeQuery();
        return resultSet.next();
    }

    //查看自己考勤信息
    public List<ClockWork> selectClock(Connection con,String  e_no) throws SQLException {
        List<ClockWork> list = new ArrayList<>();
        String sql = "SELECT * FROM(\n" +
                " SELECT\n" +
                " work_date,\n" +
                " employee_no,\n" +
                " t.employee_name,\n" +
                " clock_in_time,\n" +
                " clock_off_time,\n" +
                " CASE\n" +
                "WHEN clock_in_diff <- 120 THEN\n" +
                " '旷工'\n" +
                "WHEN clock_in_diff < 0 THEN\n" +
                " '迟到'\n" +
                "WHEN clock_in_diff > 0 THEN\n" +
                " '正常'\n" +
                "ELSE\n" +
                " '忘记打卡'\n" +
                "END AS diff_in_status,\n" +
                " CASE\n" +
                "WHEN clock_off_diff > 120 THEN\n" +
                " '旷工'\n" +
                "WHEN clock_off_diff > 0 THEN\n" +
                " '早退'\n" +
                "WHEN clock_off_diff < 0 THEN\n" +
                " '正常'\n" +
                "ELSE\n" +
                " '忘记打卡'\n" +
                "END AS diff_off_status\n" +
                "FROM\n" +
                " (\n" +
                "  SELECT\n" +
                "   q.work_date,\n" +
                "   q.employee_no,\n" +
                "   q.employee_name,\n" +
                "   clock_in_time,\n" +
                "   clock_off_time,\n" +
                "   TIMESTAMPDIFF(\n" +
                "    MINUTE,\n" +
                "    clock_in_time,\n" +
                "    CONCAT(work_date, ' 09:00:00')\n" +
                "   ) AS clock_in_diff,\n" +
                "   TIMESTAMPDIFF(\n" +
                "    MINUTE,\n" +
                "    clock_off_time,\n" +
                "    CONCAT(work_date, ' 18:00:00')\n" +
                "   ) AS clock_off_diff\n" +
                "  FROM\n" +
                "   (\n" +
                "    SELECT\n" +
                "     s.employee_no,\n" +
                "     s.employee_name,\n" +
                "     clock_in_time,\n" +
                "     clock_off_time,\n" +
                "     s.work_date\n" +
                "    FROM\n" +
                "     (\n" +
                "      SELECT\n" +
                "       employee_no,\n" +
                "       employee_name,\n" +
                "       work_date\n" +
                "      FROM\n" +
                "       t_work_date,\n" +
                "       t_employee\n" +
                "       WHERE\n" +
                "       role=0\n" +
                "       AND work_date<NOW()-1\n" +
                "       AND work_date>t_employee.hiredate\n" +
                "     ) s\n" +
                "    LEFT JOIN t_clock_info ON s.employee_no = t_clock_info.employee_no\n" +
                "    AND s.work_date = t_clock_info.clock_date\n" +
                "   ) q\n" +
                " ) t) a" +
                " where employee_no=?";

        PreparedStatement ps = con.prepareStatement(sql);
        ps.setString(1,e_no);
        ResultSet resultSet = ps.executeQuery();
        while (resultSet.next()){
            String date = resultSet.getString("work_date");
            String employee_name = resultSet.getString("employee_name");
            String in_time = resultSet.getString("clock_in_time");
            String off_time = resultSet.getString("clock_off_time");
            String diff_in_status = resultSet.getString("diff_in_status");
            String diff_off_status = resultSet.getString("diff_off_status");

            ClockWork clock = new ClockWork(date,e_no,employee_name, in_time, off_time,diff_in_status,diff_off_status);
            list.add(clock);
        }
        return list;
    }

    //查看考勤信息（模糊搜索）单独
    /*public List<ClockWork> selectClockNo(Connection con,String  e_no) throws SQLException {
        List<ClockWork> list = new ArrayList<>();
        String sql = "SELECT * FROM(\n" +
                " SELECT\n" +
                " work_date,\n" +
                " employee_no,\n" +
                " t.employee_name,\n" +
                " clock_in_time,\n" +
                " clock_off_time,\n" +
                " CASE\n" +
                "WHEN clock_in_diff <- 120 THEN\n" +
                " '旷工'\n" +
                "WHEN clock_in_diff < 0 THEN\n" +
                " '迟到'\n" +
                "WHEN clock_in_diff > 0 THEN\n" +
                " '正常'\n" +
                "ELSE\n" +
                " '忘记打卡'\n" +
                "END AS diff_in_status,\n" +
                " CASE\n" +
                "WHEN clock_off_diff > 120 THEN\n" +
                " '旷工'\n" +
                "WHEN clock_off_diff > 0 THEN\n" +
                " '早退'\n" +
                "WHEN clock_off_diff < 0 THEN\n" +
                " '正常'\n" +
                "ELSE\n" +
                " '忘记打卡'\n" +
                "END AS diff_off_status\n" +
                "FROM\n" +
                " (\n" +
                "  SELECT\n" +
                "   q.work_date,\n" +
                "   q.employee_no,\n" +
                "   q.employee_name,\n" +
                "   clock_in_time,\n" +
                "   clock_off_time,\n" +
                "   TIMESTAMPDIFF(\n" +
                "    MINUTE,\n" +
                "    clock_in_time,\n" +
                "    CONCAT(work_date, ' 09:00:00')\n" +
                "   ) AS clock_in_diff,\n" +
                "   TIMESTAMPDIFF(\n" +
                "    MINUTE,\n" +
                "    clock_off_time,\n" +
                "    CONCAT(work_date, ' 18:00:00')\n" +
                "   ) AS clock_off_diff\n" +
                "  FROM\n" +
                "   (\n" +
                "    SELECT\n" +
                "     s.employee_no,\n" +
                "     s.employee_name,\n" +
                "     clock_in_time,\n" +
                "     clock_off_time,\n" +
                "     s.work_date\n" +
                "    FROM\n" +
                "     (\n" +
                "      SELECT\n" +
                "       employee_no,\n" +
                "       employee_name,\n" +
                "       work_date\n" +
                "      FROM\n" +
                "       t_work_date,\n" +
                "       t_employee\n" +
                "       WHERE\n" +
                "       role=0\n" +
                "       AND work_date<NOW()" +
                "     ) s\n" +
                "    LEFT JOIN t_clock_info ON s.employee_no = t_clock_info.employee_no\n" +
                "    AND s.work_date = t_clock_info.clock_date\n" +
                "   ) q\n" +
                " ) t) a" +
                " where employee_no like concat('%',?,'%')";

        PreparedStatement ps = con.prepareStatement(sql);
        ps.setString(1,e_no);
        ResultSet resultSet = ps.executeQuery();
        while (resultSet.next()){
            String date = resultSet.getString("work_date");
            String employee_no = resultSet.getString("employee_no");
            String employee_name = resultSet.getString("employee_name");
            String in_time = resultSet.getString("clock_in_time");
            String off_time = resultSet.getString("clock_off_time");
            String diff_in_status = resultSet.getString("diff_in_status");
            String diff_off_status = resultSet.getString("diff_off_status");

            ClockWork clock = new ClockWork(date,employee_no,employee_name, in_time, off_time,diff_in_status,diff_off_status);
            list.add(clock);
        }
        return list;
    }
    //查看考勤信息（模糊搜索）
    public List<ClockWork> selectClockName(Connection con,String  e_name) throws SQLException {
        List<ClockWork> list = new ArrayList<>();
        String sql = "SELECT * FROM(\n" +
                " SELECT\n" +
                " work_date,\n" +
                " employee_no,\n" +
                " t.employee_name,\n" +
                " clock_in_time,\n" +
                " clock_off_time,\n" +
                " CASE\n" +
                "WHEN clock_in_diff <- 120 THEN\n" +
                " '旷工'\n" +
                "WHEN clock_in_diff < 0 THEN\n" +
                " '迟到'\n" +
                "WHEN clock_in_diff > 0 THEN\n" +
                " '正常'\n" +
                "ELSE\n" +
                " '忘记打卡'\n" +
                "END AS diff_in_status,\n" +
                " CASE\n" +
                "WHEN clock_off_diff > 120 THEN\n" +
                " '旷工'\n" +
                "WHEN clock_off_diff > 0 THEN\n" +
                " '早退'\n" +
                "WHEN clock_off_diff < 0 THEN\n" +
                " '正常'\n" +
                "ELSE\n" +
                " '忘记打卡'\n" +
                "END AS diff_off_status\n" +
                "FROM\n" +
                " (\n" +
                "  SELECT\n" +
                "   q.work_date,\n" +
                "   q.employee_no,\n" +
                "   q.employee_name,\n" +
                "   clock_in_time,\n" +
                "   clock_off_time,\n" +
                "   TIMESTAMPDIFF(\n" +
                "    MINUTE,\n" +
                "    clock_in_time,\n" +
                "    CONCAT(work_date, ' 09:00:00')\n" +
                "   ) AS clock_in_diff,\n" +
                "   TIMESTAMPDIFF(\n" +
                "    MINUTE,\n" +
                "    clock_off_time,\n" +
                "    CONCAT(work_date, ' 18:00:00')\n" +
                "   ) AS clock_off_diff\n" +
                "  FROM\n" +
                "   (\n" +
                "    SELECT\n" +
                "     s.employee_no,\n" +
                "     s.employee_name,\n" +
                "     clock_in_time,\n" +
                "     clock_off_time,\n" +
                "     s.work_date\n" +
                "    FROM\n" +
                "     (\n" +
                "      SELECT\n" +
                "       employee_no,\n" +
                "       employee_name,\n" +
                "       work_date\n" +
                "      FROM\n" +
                "       t_work_date,\n" +
                "       t_employee\n" +
                "       WHERE\n" +
                "       role=0\n" +
                "       AND work_date<NOW()" +
                "     ) s\n" +
                "    LEFT JOIN t_clock_info ON s.employee_no = t_clock_info.employee_no\n" +
                "    AND s.work_date = t_clock_info.clock_date\n" +
                "   ) q\n" +
                " ) t) a" +
                " where employee_name like concat('%',?,'%')";

        PreparedStatement ps = con.prepareStatement(sql);
        ps.setString(1,e_name);
        ResultSet resultSet = ps.executeQuery();
        while (resultSet.next()){
            String date = resultSet.getString("work_date");
            String employee_no = resultSet.getString("employee_no");
            String employee_name = resultSet.getString("employee_name");
            String in_time = resultSet.getString("clock_in_time");
            String off_time = resultSet.getString("clock_off_time");
            String diff_in_status = resultSet.getString("diff_in_status");
            String diff_off_status = resultSet.getString("diff_off_status");

            ClockWork clock = new ClockWork(date,employee_no,employee_name, in_time, off_time,diff_in_status,diff_off_status);
            list.add(clock);
        }
        return list;
    }
    //查看考勤信息（模糊搜索）
    public List<ClockWork> selectClockDate(Connection con,String  w_date) throws SQLException {
        List<ClockWork> list = new ArrayList<>();
        String sql = "SELECT * FROM(\n" +
                " SELECT\n" +
                " work_date,\n" +
                " employee_no,\n" +
                " t.employee_name,\n" +
                " clock_in_time,\n" +
                " clock_off_time,\n" +
                " CASE\n" +
                "WHEN clock_in_diff <- 120 THEN\n" +
                " '旷工'\n" +
                "WHEN clock_in_diff < 0 THEN\n" +
                " '迟到'\n" +
                "WHEN clock_in_diff > 0 THEN\n" +
                " '正常'\n" +
                "ELSE\n" +
                " '忘记打卡'\n" +
                "END AS diff_in_status,\n" +
                " CASE\n" +
                "WHEN clock_off_diff > 120 THEN\n" +
                " '旷工'\n" +
                "WHEN clock_off_diff > 0 THEN\n" +
                " '早退'\n" +
                "WHEN clock_off_diff < 0 THEN\n" +
                " '正常'\n" +
                "ELSE\n" +
                " '忘记打卡'\n" +
                "END AS diff_off_status\n" +
                "FROM\n" +
                " (\n" +
                "  SELECT\n" +
                "   q.work_date,\n" +
                "   q.employee_no,\n" +
                "   q.employee_name,\n" +
                "   clock_in_time,\n" +
                "   clock_off_time,\n" +
                "   TIMESTAMPDIFF(\n" +
                "    MINUTE,\n" +
                "    clock_in_time,\n" +
                "    CONCAT(work_date, ' 09:00:00')\n" +
                "   ) AS clock_in_diff,\n" +
                "   TIMESTAMPDIFF(\n" +
                "    MINUTE,\n" +
                "    clock_off_time,\n" +
                "    CONCAT(work_date, ' 18:00:00')\n" +
                "   ) AS clock_off_diff\n" +
                "  FROM\n" +
                "   (\n" +
                "    SELECT\n" +
                "     s.employee_no,\n" +
                "     s.employee_name,\n" +
                "     clock_in_time,\n" +
                "     clock_off_time,\n" +
                "     s.work_date\n" +
                "    FROM\n" +
                "     (\n" +
                "      SELECT\n" +
                "       employee_no,\n" +
                "       employee_name,\n" +
                "       work_date\n" +
                "      FROM\n" +
                "       t_work_date,\n" +
                "       t_employee\n" +
                "       WHERE\n" +
                "       role=0\n" +
                "       AND work_date<NOW()" +
                "     ) s\n" +
                "    LEFT JOIN t_clock_info ON s.employee_no = t_clock_info.employee_no\n" +
                "    AND s.work_date = t_clock_info.clock_date\n" +
                "   ) q\n" +
                " ) t) a" +
                " where work_date like concat('%',?,'%')";

        PreparedStatement ps = con.prepareStatement(sql);
        ps.setString(1,w_date);
        ResultSet resultSet = ps.executeQuery();
        while (resultSet.next()){
            String date = resultSet.getString("work_date");
            String employee_no = resultSet.getString("employee_no");
            String employee_name = resultSet.getString("employee_name");
            String in_time = resultSet.getString("clock_in_time");
            String off_time = resultSet.getString("clock_off_time");
            String diff_in_status = resultSet.getString("diff_in_status");
            String diff_off_status = resultSet.getString("diff_off_status");

            ClockWork clock = new ClockWork(date,employee_no,employee_name, in_time, off_time,diff_in_status,diff_off_status);
            list.add(clock);
        }
        return list;
    }*/

    //查看考勤信息（模糊搜索）
    public List<ClockWork> selectClockAll(Connection con,String  e_no,String e_name,String w_date) throws SQLException {
        List<ClockWork> list = new ArrayList<>();
        String sql = "SELECT * FROM(\n" +
                " SELECT\n" +
                " work_date,\n" +
                " employee_no,\n" +
                " t.employee_name,\n" +
                " clock_in_time,\n" +
                " clock_off_time,\n" +
                " CASE\n" +
                "WHEN clock_in_diff <- 120 THEN\n" +
                " '旷工'\n" +
                "WHEN clock_in_diff < 0 THEN\n" +
                " '迟到'\n" +
                "WHEN clock_in_diff > 0 THEN\n" +
                " '正常'\n" +
                "ELSE\n" +
                " '忘记打卡'\n" +
                "END AS diff_in_status,\n" +
                " CASE\n" +
                "WHEN clock_off_diff > 120 THEN\n" +
                " '旷工'\n" +
                "WHEN clock_off_diff > 0 THEN\n" +
                " '早退'\n" +
                "WHEN clock_off_diff < 0 THEN\n" +
                " '正常'\n" +
                "ELSE\n" +
                " '忘记打卡'\n" +
                "END AS diff_off_status\n" +
                "FROM\n" +
                " (\n" +
                "  SELECT\n" +
                "   q.work_date,\n" +
                "   q.employee_no,\n" +
                "   q.employee_name,\n" +
                "   clock_in_time,\n" +
                "   clock_off_time,\n" +
                "   TIMESTAMPDIFF(\n" +
                "    MINUTE,\n" +
                "    clock_in_time,\n" +
                "    CONCAT(work_date, ' 09:00:00')\n" +
                "   ) AS clock_in_diff,\n" +
                "   TIMESTAMPDIFF(\n" +
                "    MINUTE,\n" +
                "    clock_off_time,\n" +
                "    CONCAT(work_date, ' 18:00:00')\n" +
                "   ) AS clock_off_diff\n" +
                "  FROM\n" +
                "   (\n" +
                "    SELECT\n" +
                "     s.employee_no,\n" +
                "     s.employee_name,\n" +
                "     clock_in_time,\n" +
                "     clock_off_time,\n" +
                "     s.work_date\n" +
                "    FROM\n" +
                "     (\n" +
                "      SELECT\n" +
                "       employee_no,\n" +
                "       employee_name,\n" +
                "       work_date\n" +
                "      FROM\n" +
                "       t_work_date,\n" +
                "       t_employee\n" +
                "       WHERE\n" +
                "       role=0\n" +
                "       AND work_date<NOW()-1\n" +
                "       AND work_date>t_employee.hiredate\n" +
                "     ) s\n" +
                "    LEFT JOIN t_clock_info ON s.employee_no = t_clock_info.employee_no\n" +
                "    AND s.work_date = t_clock_info.clock_date\n" +
                "   ) q\n" +
                " ) t) a" +
                "  WHERE " +
                " work_date like concat(?,'%') and\n" +
                " employee_name like CONCAT('%',?,'%') and\n" +
                " employee_no like concat('%',?,'%') ;";

        PreparedStatement ps = con.prepareStatement(sql);
        ps.setString(1,w_date);
        ps.setString(2,e_name);
        ps.setString(3,e_no);
        ResultSet resultSet = ps.executeQuery();
        while (resultSet.next()){
            String date = resultSet.getString("work_date");
            String employee_no = resultSet.getString("employee_no");
            String employee_name = resultSet.getString("employee_name");
            String in_time = resultSet.getString("clock_in_time");
            String off_time = resultSet.getString("clock_off_time");
            String diff_in_status = resultSet.getString("diff_in_status");
            String diff_off_status = resultSet.getString("diff_off_status");

            ClockWork clock = new ClockWork(date,employee_no,employee_name, in_time, off_time,diff_in_status,diff_off_status);
            list.add(clock);
        }
        return list;
    }

    //查看所有考勤信息
    public List<ClockWork> selectClockAll(Connection con) throws SQLException {
        List<ClockWork> list = new ArrayList<>();
        String sql = "SELECT\n" +
                " work_date,\n" +
                " employee_no,\n" +
                " t.employee_name,\n" +
                " clock_in_time,\n" +
                " clock_off_time,\n" +
                " CASE\n" +
                "WHEN clock_in_diff <- 120 THEN\n" +
                " '旷工'\n" +
                "WHEN clock_in_diff < 0 THEN\n" +
                " '迟到'\n" +
                "WHEN clock_in_diff > 0 THEN\n" +
                " '正常'\n" +
                "ELSE\n" +
                " '忘记打卡'\n" +
                "END AS diff_in_status,\n" +
                " CASE\n" +
                "WHEN clock_off_diff > 120 THEN\n" +
                " '旷工'\n" +
                "WHEN clock_off_diff > 0 THEN\n" +
                " '早退'\n" +
                "WHEN clock_off_diff < 0 THEN\n" +
                " '正常'\n" +
                "ELSE\n" +
                " '忘记打卡'\n" +
                "END AS diff_off_status\n" +
                "FROM\n" +
                " (\n" +
                "  SELECT\n" +
                "   q.work_date,\n" +
                "   q.employee_no,\n" +
                "   q.employee_name,\n" +
                "   clock_in_time,\n" +
                "   clock_off_time,\n" +
                "   TIMESTAMPDIFF(\n" +
                "    MINUTE,\n" +
                "    clock_in_time,\n" +
                "    CONCAT(work_date, ' 09:00:00')\n" +
                "   ) AS clock_in_diff,\n" +
                "   TIMESTAMPDIFF(\n" +
                "    MINUTE,\n" +
                "    clock_off_time,\n" +
                "    CONCAT(work_date, ' 18:00:00')\n" +
                "   ) AS clock_off_diff\n" +
                "  FROM\n" +
                "   (\n" +
                "    SELECT\n" +
                "     s.employee_no,\n" +
                "     s.employee_name,\n" +
                "     clock_in_time,\n" +
                "     clock_off_time,\n" +
                "     s.work_date\n" +
                "    FROM\n" +
                "     (\n" +
                "      SELECT\n" +
                "       employee_no,\n" +
                "       employee_name,\n" +
                "       work_date\n" +
                "      FROM\n" +
                "       t_work_date,\n" +
                "       t_employee\n" +
                "       WHERE\n" +
                "       role=0\n" +
                "       AND work_date<NOW()-1\n" +
                "       AND work_date>t_employee.hiredate\n" +
                "     ) s\n" +
                "    LEFT JOIN t_clock_info ON s.employee_no = t_clock_info.employee_no\n" +
                "    AND s.work_date = t_clock_info.clock_date\n" +
                "   ) q\n" +
                " ) t;";

        PreparedStatement ps = con.prepareStatement(sql);
        ResultSet resultSet = ps.executeQuery();
        while (resultSet.next()){
            String date = resultSet.getString("work_date");
            String employee_no = resultSet.getString("employee_no");
            String employee_name = resultSet.getString("employee_name");
            String in_time = resultSet.getString("clock_in_time");
            String off_time = resultSet.getString("clock_off_time");
            String diff_in_status = resultSet.getString("diff_in_status");
            String diff_off_status = resultSet.getString("diff_off_status");

            ClockWork clock = new ClockWork(date,employee_no,employee_name, in_time, off_time,diff_in_status,diff_off_status);
            list.add(clock);
        }
        return list;
    }
}
